﻿using System;
using System.Collections.Generic;
using Hanhud.Pattern;
using System.Data;
using System.Data.SqlClient;
using Hanhud.Data;


namespace Hanhud.Web.Data.Providers
{
    internal class CAuctionProvider
    {
        private CDbAccess _db = new CDbAccess("Aom", string.Empty);

        public static CAuctionProvider Instance
        {
            get { return CPageSingleton<CAuctionProvider>.Instance; }
        }

        internal DataRow GetPropertyInfo(string propertyID)
        {
            DataTable tblProperty = new DataTable("Properties");

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Properties_GetPropertyInfo"));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@PropertyID", SqlDbType.VarChar, 50).Value = propertyID;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                tblProperty.Load(reader, LoadOption.Upsert);
            }

            return tblProperty.Rows.Count > 0 ? tblProperty.Rows[0] : null;
        }

        internal DataTable GetPropertyHighlights(string propertyID)
        {
            DataTable tblHighlight = new DataTable("PropertyHighlights");

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Properties_GetHighlights"));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@PropertyID", SqlDbType.VarChar, 50).Value = propertyID;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                tblHighlight.Load(reader, LoadOption.Upsert);
            }

            return tblHighlight;
        }

        internal DataTable GetFilterProperties(
            Guid ownerID, Guid brokerID,
            int propertyForList,
            int propertyTypeList,
            DateTime startDate,
            DateTime endDate,
            int statusList,
            string keyword,
            bool exactWord,
            int provinceCode, int districtCode, int communeCode,
            float fromPrice, float toPrice,
            float fromSize, float toSize,
            int fromNumOfBedRoom, int toNumOfBedRoom,
            int directionList,
            int pageIndex,
            int pageSize,
            out int resultCount)
        {
            // Mac dinh resultCount
            resultCount = 0;

            DataTable tblProperty = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Properties_FilterProperties"));
            cmd.CommandType = CommandType.StoredProcedure;

            #region Command parameters
            cmd.Parameters.Add("@OwnerID", SqlDbType.UniqueIdentifier).Value = ownerID;
            cmd.Parameters.Add("@BrokerID", SqlDbType.UniqueIdentifier).Value = brokerID;
            cmd.Parameters.Add("@PropertyForList", SqlDbType.Int).Value = propertyForList;
            cmd.Parameters.Add("@PropertyTypeList", SqlDbType.Int).Value = propertyTypeList;
            cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = startDate == DateTime.MinValue ? null : startDate.ToLongDateString();
            cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = endDate == DateTime.MaxValue ? null : endDate.ToLongDateString();
            cmd.Parameters.Add("@StatusList", SqlDbType.Int).Value = statusList;
            cmd.Parameters.Add("@Keyword", SqlDbType.NVarChar, 256).Value = keyword;
            cmd.Parameters.Add("@ExactWord", SqlDbType.Bit).Value = exactWord;
            cmd.Parameters.Add("@ProvinceCode", SqlDbType.Int).Value = provinceCode;
            cmd.Parameters.Add("@DistrictCode", SqlDbType.Int).Value = districtCode;
            cmd.Parameters.Add("@CommuneCode", SqlDbType.Int).Value = communeCode;
            cmd.Parameters.Add("@FromPrice", SqlDbType.Float).Value = fromPrice;
            cmd.Parameters.Add("@ToPrice", SqlDbType.Float).Value = toPrice;
            cmd.Parameters.Add("@FromSize", SqlDbType.Float).Value = fromSize;
            cmd.Parameters.Add("@ToSize", SqlDbType.Float).Value = toSize;
            cmd.Parameters.Add("@FromNumOfBedRoom", SqlDbType.Int).Value = fromNumOfBedRoom;
            cmd.Parameters.Add("@ToNumOfBedRoom", SqlDbType.Int).Value = toNumOfBedRoom;
            cmd.Parameters.Add("@DirectionList", SqlDbType.Int).Value = directionList;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@ResultCount", SqlDbType.Int).Direction = ParameterDirection.Output; 
            #endregion

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblProperty.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult), LoadOption.Upsert);
            }

            // Lay tong ket qua
            resultCount = (int)cmd.Parameters["@ResultCount"].Value;

            return tblProperty;
        }

        internal DataTable GetPropertyPhotos(string propertyID)
        {
            DataTable tblPropertyPhoto = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("PropertyPhotos_GetPropertyPhotos"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PropertyID", SqlDbType.VarChar, 50).Value = propertyID;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                tblPropertyPhoto.Load(_db.ExecuteReader(cmd, CommandBehavior.SingleResult), LoadOption.Upsert);
            }

            return tblPropertyPhoto;
        }

        internal bool DeletePropertyPhotos(string photoIDList)
        {
            bool success = false;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("PropertyPhotos_DeletePropertyPhotos"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PhotoIDList", SqlDbType.VarChar, 1000).Value = photoIDList;

            return success;
        }

        internal void IncrementPropertyViewCount(string propertyID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Properties_IncrementViewCount"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PropertyID", SqlDbType.VarChar, 50).Value = propertyID;
            int retv = _db.ExecuteNonQuery(cmd);
        }
    }
}
